package com.example.clock.Export;
import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.example.clock.basicClass.Task;
import com.example.clock.controlClass.SqlController;
import com.opencsv.CSVWriter;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;


public class ExportData {
    @SuppressLint("Range")
    public static String exportAllTaskToCSV(SQLiteDatabase db, Context context) {
        //获取数据
        Cursor c = db.query("Tasks", null, null, null, null, null, null);


            //有权限

            //文件路径
            LocalDateTime ldt = LocalDateTime.now();
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String dtString = dateTimeFormatter.format(ldt);
            dtString.replace(" ", "");//去空格
            String csvFilePath = context.getExternalFilesDir(null)+ File.separator+ "task_export"+File.separator+"exported_all_task_" + dtString + ".csv";
            // 创建CSV文件写入器
        try {
            // 确保目录存在，如果不存在则尝试创建
            File directory = new File(csvFilePath).getParentFile();
            if (!directory.exists()) {
                directory.mkdirs();
            }

            // 创建CSVWriter并写入文件
            try (CSVWriter writer = new CSVWriter(new FileWriter(csvFilePath))) {
                // 写入文件头部
                String[] header = c.getColumnNames();
                writer.writeNext(header);
                // 写入数据行
                while (c.moveToNext()) {
                    String[] row = new String[14];
                    for (int i = 0; i < 14; i++) {
                        row[i] = c.getString(c.getColumnIndex(header[i]));
                    }
                    writer.writeNext(row);
                }
            } catch (IOException e) {
                e.printStackTrace();
                return e.toString();
            }

            return csvFilePath;
        } catch (SQLException e) {
            e.printStackTrace();
            return e.toString();
        }
            //return "文件成功创建："+csvFilePath;
    }
    //导出特定日期的task
    @SuppressLint("Range")
    public static String exportTaskToCSVByDate(SQLiteDatabase db, Context context,String date) {
        //获取数据
        Cursor c = db.query("Tasks", null,"date = "+date, null, null, null, null);
        //有权限

        //文件路径
        LocalDateTime ldt = LocalDateTime.now();
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String dtString = dateTimeFormatter.format(ldt);
        dtString.replace(" ", "");//去空格
        String csvFilePath = context.getExternalFilesDir(null)+ File.separator+ "task_export"+File.separator+"exported_task_in_"+date+"_" + dtString + ".csv";
        // 创建CSV文件写入器
        try {
            // 确保目录存在，如果不存在则尝试创建
            File directory = new File(csvFilePath).getParentFile();
            if (!directory.exists()) {
                directory.mkdirs();
            }

            // 创建CSVWriter并写入文件
            try (CSVWriter writer = new CSVWriter(new FileWriter(csvFilePath))) {
                // 写入文件头部
                String[] header = c.getColumnNames();
                writer.writeNext(header);
                // 写入数据行
                while (c.moveToNext()) {
                    String[] row = new String[14];
                    for (int i = 0; i < 14; i++) {
                        row[i] = c.getString(c.getColumnIndex(header[i]));
                    }
                    writer.writeNext(row);
                }
            } catch (IOException e) {
                e.printStackTrace();
                return e.toString();
            }

            return csvFilePath;
        } catch (SQLException e) {
            e.printStackTrace();
            return e.toString();
        }
        //return "文件成功创建："+csvFilePath;
    }
    @SuppressLint("Range")
    public static String exportClockedTaskToCSV(SQLiteDatabase db, Context context) {
        //获取数据
        Cursor c = db.query("Tasks",null,"clockedTask = 1", null, null, null, null);
        //有权限
        int m = c.getCount();
        //文件路径
        LocalDateTime ldt = LocalDateTime.now();
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String dtString = dateTimeFormatter.format(ldt);
        dtString.replace(" ", "");//去空格
        String csvFilePath = context.getExternalFilesDir(null)+ File.separator+ "task_export"+File.separator+"exported_Clocked_task_" + dtString + ".csv";
        // 创建CSV文件写入器
        try {
            // 确保目录存在，如果不存在则尝试创建
            File directory = new File(csvFilePath).getParentFile();
            if (!directory.exists()) {
                directory.mkdirs();
            }
            c.moveToNext();
            Task t = SqlController.cursorToTask(c);
            t.getEndTime();
            // 创建CSVWriter并写入文件
            try (CSVWriter writer = new CSVWriter(new FileWriter(csvFilePath))) {
                // 写入文件头部
                String[] header = c.getColumnNames();
                writer.writeNext(header);
                // 写入数据行
                do{
                    String[] row = new String[14];
                    for (int i = 0; i < 14; i++) {
                        row[i] = c.getString(c.getColumnIndex(header[i]));
                    }
                    writer.writeNext(row);
                }while (c.moveToNext());
            } catch (IOException e) {
                e.printStackTrace();
                return e.toString();
            }

            return csvFilePath;
        } catch (SQLException e) {
            e.printStackTrace();
            return e.toString();
        }
        //return "文件成功创建："+csvFilePath;
    }
    @SuppressLint("Range")
    public static String exportTaskByPlanToCSV(SQLiteDatabase db, Context context,String planId,String planName) {
        //获取数据
        Cursor c = db.query("Tasks", null, "planId = "+planId, null, null, null, null);
        //文件路径
        LocalDateTime ldt = LocalDateTime.now();
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String dtString = dateTimeFormatter.format(ldt);
        dtString.replace(" ", "");//去空格
        String csvFilePath = context.getExternalFilesDir(null)+ File.separator+ "plan_export"+File.separator+"task_with_plan_"+planName+"_" + dtString + ".csv";
        // 创建CSV文件写入器
        try {
            // 确保目录存在，如果不存在则尝试创建
            File directory = new File(csvFilePath).getParentFile();
            if (!directory.exists()) {
                directory.mkdirs();
            }

            // 创建CSVWriter并写入文件
            try (CSVWriter writer = new CSVWriter(new FileWriter(csvFilePath))) {
                // 写入文件头部
                String[] header = c.getColumnNames();
                writer.writeNext(header);
                // 写入数据行
                while (c.moveToNext()) {
                    String[] row = new String[14];
                    for (int i = 0; i < 14; i++) {
                        row[i] = c.getString(c.getColumnIndex(header[i]));
                    }
                    writer.writeNext(row);
                }
            } catch (IOException e) {
                e.printStackTrace();
                return e.toString();
            }

            return csvFilePath;
        } catch (SQLException e) {
            e.printStackTrace();
            return e.toString();
        }
        //return "文件成功创建："+csvFilePath;
    }
    @SuppressLint("Range")
    public static String exportAllPlanToCSV(SQLiteDatabase db, Context context) {
        //获取数据
        Cursor c = db.query("Plans", null, null, null, null, null, null);
        //文件路径
        LocalDateTime ldt = LocalDateTime.now();
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String dtString = dateTimeFormatter.format(ldt);
        dtString.replace(" ", "");//去空格
        String csvFilePath = context.getExternalFilesDir(null)+ File.separator+ "plan_export"+File.separator+"exported_all_plan_" + dtString + ".csv";
        // 创建CSV文件写入器
        try {
            // 确保目录存在，如果不存在则尝试创建
            File directory = new File(csvFilePath).getParentFile();
            if (!directory.exists()) {
                directory.mkdirs();
            }

            // 创建CSVWriter并写入文件
            try (CSVWriter writer = new CSVWriter(new FileWriter(csvFilePath))) {
                // 写入文件头部
                String[] header = c.getColumnNames();
                writer.writeNext(header);
                // 写入数据行
                while (c.moveToNext()) {
                    String[] row = new String[10];
                    for (int i = 0; i < 10; i++) {
                        row[i] = c.getString(c.getColumnIndex(header[i]));
                    }
                    writer.writeNext(row);
                }
            } catch (IOException e) {
                e.printStackTrace();
                return e.toString();
            }

            return csvFilePath;
        } catch (SQLException e) {
            e.printStackTrace();
            return e.toString();
        }
        //return "文件成功创建："+csvFilePath;
    }
    public static String convertCsvToExcel(String csvFilePath) {
        String excelFilePath = "null";
        try (BufferedReader br = new BufferedReader(new FileReader(csvFilePath));
             Workbook workbook = new XSSFWorkbook()) {

            Sheet sheet = workbook.createSheet("Sheet1");
            String line;
            int rowNumber = 0;

            while ((line = br.readLine()) != null) {
                Row row = sheet.createRow(rowNumber++);
                String[] data = line.split(",");

                for (int i = 0; i < data.length; i++) {
                    Cell cell = row.createCell(i);
                    cell.setCellValue(data[i]);
                }
            }
            excelFilePath = csvFilePath.substring(0,csvFilePath.length()-4) + ".xlsx";
            try (FileOutputStream fileOutputStream = new FileOutputStream(excelFilePath.toString())) {
                workbook.write(fileOutputStream);
            }

            System.out.println("CSV file converted to Excel successfully.");

        } catch (IOException e) {
            e.printStackTrace();
            System.err.println("Error converting CSV to Excel: " + e.getMessage());
        }
        return excelFilePath;
    }

}
